<?php

/**
 * stores actions.
 *
 * @package    retailbrain
 * @subpackage stores
 * @author     Your name here
 * @version    SVN: $Id: actions.class.php 23810 2009-11-12 11:07:44Z Kris.Wallsmith $
 */
class storesActions extends sfActions {

    /**
     * Termina la session por inactividad
     */
    public function preExecute() {
        if (!$this->getUser()->isAuthenticated()) {
            $this->getUser()->setAuthenticated(false);
            $this->getUser()->getAttributeHolder()->clear();
            $this->redirect('/login');
        }
    }

    /**
     * Executes index action
     *
     * @param sfRequest $request A request object
     */
    public function executeIndex(sfWebRequest $request) {

        $this->title = "Locales";
        $this->breadcrumbs = array(array("Locales", "stores/index"));

        $user = $this->getUser()->getAttribute('id_users');


        $channelsID = myFunctionsTables::getFilterTableIds('Locales', 'Channels', $user);
        $customersID = myFunctionsTables::getFilterTableIds('Locales', 'Customers', $user);
        $formatsID = myFunctionsTables::getFilterTableIds('Locales', 'Formats', $user);
        $codeFormatsID = myFunctionsTables::getFilterTableIds('Locales', 'CodeFormats', $user);
        $concessionsID = myFunctionsTables::getFilterTableIds('Locales', 'Concessions', $user);
        $locationsID = myFunctionsTables::getFilterTableIds('Locales', 'Locations', $user);
        $clustersID = myFunctionsTables::getFilterTableIds('Locales', 'Clusters', $user);
        $regionesID = myFunctionsTables::getFilterTableIds('Locales', 'Regiones', $user);
        $provinciasID = myFunctionsTables::getFilterTableIds('Locales', 'Provincias', $user);
        $comunasID = myFunctionsTables::getFilterTableIds('Locales', 'Comunas', $user);



        $q = Doctrine_Query::create()
                ->from('Stores s')
                ->innerJoin('s.UserStores us')
                ->innerJoin('s.Channels ch')
                ->innerJoin('s.Customers c')
                ->innerJoin('s.Formats f')
                ->innerJoin('s.CodeFormats cf')
                ->innerJoin('s.Concessions cc')
                ->innerJoin('s.Locations l')
                ->innerJoin('s.Comunas co')
                ->innerJoin('s.Provincias p')
                ->innerJoin('s.Regiones r')
                ->innerJoin('s.Paises pa')
                ->innerJoin('s.Clusters cl');

        if ($channelsID[0] != '') {
            $q->whereIn('s.id_channel', $channelsID);
        }
        if ($customersID[0] != '') {
            $q->whereIn('s.id_customer', $customersID);
        }
        if ($formatsID[0] != '') {
            $q->whereIn('s.id_format', $formatsID);
        }
        if ($codeFormatsID[0] != '') {
            $q->whereIn('s.id_codeformat', $codeFormatsID);
        }
        if ($concessionsID[0] != '') {
            $q->whereIn('s.id_concession', $concessionsID);
        }
        if ($locationsID[0] != '') {
            $q->whereIn('s.id_location', $locationsID);
        }
        if ($clustersID[0] != '') {
            $q->whereIn('s.id_cluster', $clustersID);
        }
        if ($regionesID[0] != '') {
            $q->whereIn('s.id_region', $regionesID);
        }
        if ($provinciasID[0] != '') {
            $q->whereIn('s.id_provincia', $provinciasID);
        }
        if ($comunasID[0] != '') {
            $q->whereIn('s.id_comuna', $comunasID);
        }

        $q->orderBy('s.name desc');


        $this->stores = $q->fetchArray();


        $q = Doctrine_Query::create()
                ->from('UserTable ut')
                ->leftJoin('ut.UserTableOrder uto')
                ->where('ut.table_user = ?', array('Locales'))
                ->addWhere('uto.id_users = ?', array($this->getUser()->getAttribute('id_users')))
                ->orderBy('uto.orden');

        $this->userTableOrder = $q->fetchArray();

        $this->userOrderCount = count($q->fetchArray());


        if (!$this->userOrderCount) {
            $q = Doctrine_Query::create()
                    ->from('UserTable ut')
                    ->leftJoin('ut.UserTableOrder uto')
                    ->where('ut.table_user = ?', array('Locales'))
                    ->orderBy('ut.id_tablecolumn');

            $this->userTableOrder = $q->fetchArray();
        }



        $this->channels = myFunctionsTables::getFilterTable('Locales', 'Channels', 'id_channel', 'checked', $user);
        $this->customers = myFunctionsTables::getFilterTable('Locales', 'Customers', 'id_customer', 'checked', $user);
        $this->formats = myFunctionsTables::getFilterTable('Locales', 'Formats', 'id_format', 'checked', $user);
        $this->codeFormats = myFunctionsTables::getFilterTable('Locales', 'CodeFormats', 'id_codeformat', 'checked', $user);
        $this->concessions = myFunctionsTables::getFilterTable('Locales', 'Concessions', 'id_concession', 'checked', $user);
        $this->locations = myFunctionsTables::getFilterTable('Locales', 'Locations', 'id_location', 'checked', $user);
        $this->clusters = myFunctionsTables::getFilterTable('Locales', 'Clusters', 'id_cluster', 'checked', $user);
        $this->regiones = myFunctionsTables::getFilterTable('Locales', 'Regiones', 'id_region', 'selected', $user);
        $this->provincias = myFunctionsTables::getFilterTable('Locales', 'Provincias', 'id_provincia', 'selected', $user);
        $this->comunas = myFunctionsTables::getFilterTable('Locales', 'Comunas', 'id_comuna', 'selected', $user);

        $this->caps = $this->getUser()->getAttribute('caps');
    }

    /**
     * Executes index action
     *
     * @param sfRequest $request A request object
     */
    public function executeCreateOrder(sfWebRequest $request) {


        $order = $request->getParameter('order');
        $visible = $request->getParameter('visible');
        $table_column = $request->getParameter('tableColumn');

        $user = $this->getUser()->getAttribute('id_users');

        myFunctionsTables::setOrderTable($table_column, $order, $visible, $user, "Locales");

        $this->redirect('/stores/index');
    }

    /**
     * Executes index action
     *
     * @param sfRequest $request A request object
     */
    public function executeCreateFilter(sfWebRequest $request) {

        $table_column = $request->getParameter('tableColumn');

        $user = $this->getUser()->getAttribute('id_users');

        myFunctionsTables::setFilterTable($table_column, 'Channels', $request->getParameter('Channels'), $user);
        myFunctionsTables::setFilterTable($table_column, 'Customers', $request->getParameter('Customers'), $user);
        myFunctionsTables::setFilterTable($table_column, 'Formats', $request->getParameter('Formats'), $user);
        myFunctionsTables::setFilterTable($table_column, 'CodeFormats', $request->getParameter('CodeFormats'), $user);
        myFunctionsTables::setFilterTable($table_column, 'Concessions', $request->getParameter('Concessions'), $user);
        myFunctionsTables::setFilterTable($table_column, 'Locations', $request->getParameter('Locations'), $user);
        myFunctionsTables::setFilterTable($table_column, 'Clusters', $request->getParameter('Clusters'), $user);
        myFunctionsTables::setFilterTable($table_column, 'Regiones', $request->getParameter('Regiones'), $user);
        myFunctionsTables::setFilterTable($table_column, 'Provincias', $request->getParameter('Provincias'), $user);
        myFunctionsTables::setFilterTable($table_column, 'Comunas', $request->getParameter('Comunas'), $user);

        $this->redirect('/stores/index');
    }

    function executeGetRegionesPorPais(sfRequest $request) {
        $this->setLayout(false);

        $paises = $this->getRequestParameter("pais");

        $regiones = Doctrine_Query::create()
                ->from('Regiones r')
                ->whereIn('r.id_pais', $paises)
                ->orderBy('r.name')
                ->fetchArray();
        

        $out = "<option value = \"\">Seleccione </option>";
        foreach ($regiones as $value) {
            $out .= "<option value = \"" . $value["id_region"] . "\">" . $value["name"] . "</option>";
        }


        $this->getResponse()->setContent($out);

        return sfView::NONE;
    }

    function executeGetProvinciasPorRegiones(sfRequest $request) {
        $this->setLayout(false);

        $regiones = $this->getRequestParameter("region");

        $provincias = Doctrine_Query::create()
                ->from('Provincias p')
                ->whereIn('p.id_region', $regiones)
                ->orderBy('p.name')
                ->fetchArray();

        $out = "<option value = \"\">Seleccione </option>";
        foreach ($provincias as $value) {
            $out .= "<option value = \"" . $value["id_provincia"] . "\">" . $value["name"] . "</option>";
        }


        $this->getResponse()->setContent($out);

        return sfView::NONE;
    }

    function executeGetComunasPorProvincias(sfRequest $request) {
        $this->setLayout(false);

        $provincias = $this->getRequestParameter("provincia");

        $comunas = Doctrine_Query::create()
                ->from('Comunas c')
                ->whereIn('c.id_provincia', $provincias)
                ->orderBy('c.name')
                ->fetchArray();

        $out = "<option value = \"\">Seleccione </option>";
        foreach ($comunas as $value) {
            $out .= "<option value = \"" . $value["id_comuna"] . "\">" . $value["name"] . "</option>";
        }


        $this->getResponse()->setContent($out);

        return sfView::NONE;
    }

    /**
     * Executes index action
     *
     * @param sfRequest $request A request object
     */
    public function executeClearFilter(sfWebRequest $request) {

        $this->setLayout(false);
        $user = $this->getUser()->getAttribute('id_users');

        $_data = Doctrine_Query::create()
                ->update('UserTableFilter utf')
                ->set('utf.column_ids', '?', '')
                ->where('utf.id_users = ?', array($user))
                ->execute();

        $this->redirect('/stores/index');
    }

    /**
     * Executes index action
     *
     * @param sfRequest $request A request object
     */
    public function executeClearOrder(sfWebRequest $request) {

        $this->setLayout(false);
        $user = $this->getUser()->getAttribute('id_users');

        $this->forward404Unless($order = Doctrine::getTable('UserTableOrder')->findBySql("id_users = ? and table_user = ?", array($user,"Locales")));
        try {
            $order->delete();
        } catch (Exception $e) {
            //$this->redirect('cliente/index');
        }

        $this->redirect('/stores/index');
    }

    
    
    
    public function executeExcelStores(sfWebRequest $request) {

        $objPHPExcel = new PHPExcel();

        // Set properties
        $objPHPExcel->getProperties()->setCreator("RetailBrain");
        $objPHPExcel->getActiveSheet()->getPageSetup()->setOrientation(PHPExcel_Worksheet_PageSetup::ORIENTATION_LANDSCAPE);
        $objPHPExcel->getActiveSheet()->getPageSetup()->setPaperSize(PHPExcel_Worksheet_PageSetup::PAPERSIZE_A4);
        $objPHPExcel->getActiveSheet()->getPageSetup()->setFitToWidth(1);
        $objPHPExcel->getActiveSheet()->getPageSetup()->setFitToHeight(0);

        // Add some data
        $objPHPExcel->setActiveSheetIndex(0);
        $objPHPExcel->getActiveSheet()->setCellValueByColumnAndRow(0,1, 'CANAL');
        $objPHPExcel->getActiveSheet()->setCellValueByColumnAndRow(1,1,  'CLIENTE');
        $objPHPExcel->getActiveSheet()->setCellValueByColumnAndRow(2,1,  'CADENA / FORMATO');
        $objPHPExcel->getActiveSheet()->setCellValueByColumnAndRow(3,1,  'CÓDIGO FORMATO');
        $objPHPExcel->getActiveSheet()->setCellValueByColumnAndRow(4,1,  'CONCESIÓN');
        $objPHPExcel->getActiveSheet()->setCellValueByColumnAndRow(5,1,  'UBICACIÓN');
        $objPHPExcel->getActiveSheet()->setCellValueByColumnAndRow(6,1,  'CÓDIGO PROVEEDOR');
        $objPHPExcel->getActiveSheet()->setCellValueByColumnAndRow(7,1,  'CÓDIGO LOCAL');
        $objPHPExcel->getActiveSheet()->setCellValueByColumnAndRow(8,1,  'CÓDIGO RB');
        $objPHPExcel->getActiveSheet()->setCellValueByColumnAndRow(9,1,  'NOMBRE');
        $objPHPExcel->getActiveSheet()->setCellValueByColumnAndRow(10,1,  'DIRECCIÓN');
        $objPHPExcel->getActiveSheet()->setCellValueByColumnAndRow(11,1,  'COMUNA');
        $objPHPExcel->getActiveSheet()->setCellValueByColumnAndRow(12,1,  'PROVINCIA');
        $objPHPExcel->getActiveSheet()->setCellValueByColumnAndRow(13,1,  'REGIÓN');
        $objPHPExcel->getActiveSheet()->setCellValueByColumnAndRow(14,1,  'PAÍS');
        $objPHPExcel->getActiveSheet()->setCellValueByColumnAndRow(15,1,  'SUPERFICIE');
        $objPHPExcel->getActiveSheet()->setCellValueByColumnAndRow(16,1,  'CLUSTER');
        $objPHPExcel->getActiveSheet()->setCellValueByColumnAndRow(17,1,  'RESPONSABLE 1');
        $objPHPExcel->getActiveSheet()->setCellValueByColumnAndRow(18,1,  'RESPONSABLE 2');
        $objPHPExcel->getActiveSheet()->setCellValueByColumnAndRow(19,1,  'RESPONSABLE 3');
        $objPHPExcel->getActiveSheet()->setCellValueByColumnAndRow(20,1,  'GERENTE');
        $objPHPExcel->getActiveSheet()->setCellValueByColumnAndRow(21,1,  'ADMINISTRADOR');
        $objPHPExcel->getActiveSheet()->setCellValueByColumnAndRow(22,1,  'VTA PROMEDIO');
        
        
        
        
        
        $user = $this->getUser()->getAttribute('id_users');


        $channelsID = myFunctionsTables::getFilterTableIds('Locales', 'Channels', $user);
        $customersID = myFunctionsTables::getFilterTableIds('Locales', 'Customers', $user);
        $formatsID = myFunctionsTables::getFilterTableIds('Locales', 'Formats', $user);
        $codeFormatsID = myFunctionsTables::getFilterTableIds('Locales', 'CodeFormats', $user);
        $concessionsID = myFunctionsTables::getFilterTableIds('Locales', 'Concessions', $user);
        $locationsID = myFunctionsTables::getFilterTableIds('Locales', 'Locations', $user);
        $clustersID = myFunctionsTables::getFilterTableIds('Locales', 'Clusters', $user);
        $regionesID = myFunctionsTables::getFilterTableIds('Locales', 'Regiones', $user);
        $provinciasID = myFunctionsTables::getFilterTableIds('Locales', 'Provincias', $user);
        $comunasID = myFunctionsTables::getFilterTableIds('Locales', 'Comunas', $user);



        $q = Doctrine_Query::create()
                ->from('Stores s')
                ->innerJoin('s.Channels ch')
                ->innerJoin('s.Customers c')
                ->innerJoin('s.Formats f')
                ->innerJoin('s.CodeFormats cf')
                ->innerJoin('s.Concessions cc')
                ->innerJoin('s.Locations l')
                ->innerJoin('s.Comunas co')
                ->innerJoin('s.Provincias p')
                ->innerJoin('s.Regiones r')
                ->innerJoin('s.Paises pa')
                ->innerJoin('s.Clusters cl');

        if ($channelsID[0] != '') {
            $q->whereIn('s.id_channel', $channelsID);
        }
        if ($customersID[0] != '') {
            $q->whereIn('s.id_customer', $customersID);
        }
        if ($formatsID[0] != '') {
            $q->whereIn('s.id_format', $formatsID);
        }
        if ($codeFormatsID[0] != '') {
            $q->whereIn('s.id_codeformat', $codeFormatsID);
        }
        if ($concessionsID[0] != '') {
            $q->whereIn('s.id_concession', $concessionsID);
        }
        if ($locationsID[0] != '') {
            $q->whereIn('s.id_location', $locationsID);
        }
        if ($clustersID[0] != '') {
            $q->whereIn('s.id_cluster', $clustersID);
        }
        if ($regionesID[0] != '') {
            $q->whereIn('s.id_region', $regionesID);
        }
        if ($provinciasID[0] != '') {
            $q->whereIn('s.id_provincia', $provinciasID);
        }
        if ($comunasID[0] != '') {
            $q->whereIn('s.id_comuna', $comunasID);
        }

        $q->orderBy('s.name desc');


        $stores = $q->fetchArray();
        
        
        $i = 2;
        foreach ($stores as $value){

            $objPHPExcel->getActiveSheet()->setCellValue('A' . $i, $value['Channels']['name']);
            $objPHPExcel->getActiveSheet()->setCellValue('B' . $i, $value['Customers']['name']);
            $objPHPExcel->getActiveSheet()->setCellValue('C' . $i, $value['Formats']['name']);
            $objPHPExcel->getActiveSheet()->setCellValue('D' . $i, $value['CodeFormats']['name']);
            $objPHPExcel->getActiveSheet()->setCellValue('E' . $i, $value['Concessions']['name']);
            $objPHPExcel->getActiveSheet()->setCellValue('F' . $i, $value['Locations']['name']);
            $objPHPExcel->getActiveSheet()->setCellValue('G' . $i, $value['code_supplier']);
            $objPHPExcel->getActiveSheet()->setCellValue('H' . $i, $value['code_store']);
            $objPHPExcel->getActiveSheet()->setCellValue('I' . $i, $value['id_store']);
            $objPHPExcel->getActiveSheet()->setCellValue('J' . $i, $value['name']);
            $objPHPExcel->getActiveSheet()->setCellValue('K' . $i, $value['address']);
            $objPHPExcel->getActiveSheet()->setCellValue('L' . $i, $value['Comunas']['name']);
            $objPHPExcel->getActiveSheet()->setCellValue('M' . $i, $value['Provincias']['name']);
            $objPHPExcel->getActiveSheet()->setCellValue('N' . $i, $value['Regiones']['name_short']);
            $objPHPExcel->getActiveSheet()->setCellValue('O' . $i, $value['Paises']['name']);
            $objPHPExcel->getActiveSheet()->setCellValue('P' . $i, $value['surface']);
            $objPHPExcel->getActiveSheet()->setCellValue('Q' . $i, $value['Clusters']['name']);
            $objPHPExcel->getActiveSheet()->setCellValue('R' . $i, $value['responsablea']);
            $objPHPExcel->getActiveSheet()->setCellValue('S' . $i, $value['responsableb']);
            $objPHPExcel->getActiveSheet()->setCellValue('T' . $i, $value['responsablec']);
            $objPHPExcel->getActiveSheet()->setCellValue('U' . $i, $value['manager']);
            $objPHPExcel->getActiveSheet()->setCellValue('V' . $i, $value['administrator']);
            $objPHPExcel->getActiveSheet()->setCellValue('W' . $i, $value['averange_sales']);
            
            $i++;
        }
        
        $objPHPExcel->getActiveSheet()->setTitle('Locales');

        header('Content-Type: application/vnd.openxmlformats-officedocument.spreadsheetml.sheet');
        header('Content-Disposition: attachment;filename="locales.xlsx"');
        header('Cache-Control: max-age=0');

        $objWriter = PHPExcel_IOFactory::createWriter($objPHPExcel, 'Excel2007');
        $objWriter->save('php://output');
        exit;

      return sfview::NONE;
    }
}
